import com.bunde.repo.InventoryExcelManager2;
import com.sun.corba.se.impl.ior.WireObjectKeyTemplate;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;

import javax.xml.crypto.Data;
import java.io.*;
import java.sql.Array;
import java.util.*;

public class Main {
    public static void main(String args[]){

        // 生成文件的行数和列数， 时间复杂度：200000 * 50 = 100 *10000
        int row = 20 * 10000, col = 20;

        // 计时
        long startTime = System.currentTimeMillis();

//        Workbook wb = new XSSFWorkbook();
        // SXSSFWorkbook中实现了缓存机制，避免内存溢出
        Workbook wb = new SXSSFWorkbook();
        Sheet sheet = wb.createSheet("sale_report");
        String filename = "data.xlsx";

        // 表头
        Row row1 = sheet.createRow(0);
        for(int i = 0; i < col; i++){
            row1.createCell(i).setCellValue("第" + i + "列");
        }

        // 数据填充
        for(int i = 1; i <= row; i++){
            Row tRow = sheet.createRow(i);
//            System.out.println("set data row = " + i);
            for(int j = 0; j <= col ;j++){
                tRow.createCell(j).setCellValue(i + j);
            }
        }

        long endTime = System.currentTimeMillis();
        System.out.println("插入数据量：row = " + row + ", col = " + col + ", 耗时(ms): " + (endTime - startTime));
        startTime = endTime;
        try(OutputStream outputStream = new FileOutputStream(filename)){
            wb.write(outputStream);
            wb.close();
        }catch (IOException e){
            System.out.println("error");
            e.printStackTrace();
        }

        endTime = System.currentTimeMillis();
        System.out.println("IO耗时：row = " + row + ", col = " + col + ", 耗时(ms): " + (endTime - startTime));



    }

    @Test
    // .xls表格数据写入
    public void xlsFun(){
        Workbook wb = new HSSFWorkbook();
        wb.createSheet("sale_report");
        wb.createSheet("库存信息报表");
        try(OutputStream outputStream = new FileOutputStream("excel_test1.xls")){
            wb.write(outputStream);
            wb.close();
        }catch (IOException e){
            System.out.println("error");
            e.printStackTrace();
        }
    }

    @Test
    // .xlsx表格数据写入
    public void xlsxFun(){
//        poi-ooxml jar包中提供对于XSSFWorkbook的实现
        Workbook wb = new XSSFWorkbook();
        wb.createSheet("sale_report");
        wb.createSheet("库存信息报表");
        try(OutputStream outputStream = new FileOutputStream("excel_test1.xlsx")){
            wb.write(outputStream);
            wb.close();
        }catch (IOException e){
            System.out.println("error");
            e.printStackTrace();
        }
    }

    @Test
    // 创建单元格
    public void creatCellFun(){
        Workbook wb = new HSSFWorkbook();
        Sheet sheet1 = wb.createSheet("库存报表");
        // header, 表头
        Row row = sheet1.createRow(0);
//        Row row = sheet1.getRow(0);
        row.createCell(0).setCellValue("渠道");
        row.createCell(1).setCellValue("国家");
        row.createCell(2).setCellValue("库存");
        row.createCell(3).setCellValue("统计日期");
        // 指定表格某一列的宽度
        sheet1.setColumnWidth(3, 5000);
            // ... other

        // body， 主体数据
        // 行数
        int baseNum = 1;
        int rowNum = 10;

        CreationHelper creationHelper = wb.getCreationHelper();
        // 日期单元格式
        for(int i = 0; i < rowNum; i++){
            Row tRow = sheet1.createRow(baseNum + i);
            tRow.createCell(0).setCellValue("独立站");
            tRow.createCell(1).setCellValue("中国");
            tRow.createCell(2).setCellValue(100);
            // 日期时间格式处理
            Cell cell = tRow.createCell(3);
            cell.setCellValue(new Date());
            // 设置单元格格式以及样式
            CellStyle cs = wb.createCellStyle();
            cs.setDataFormat(creationHelper.createDataFormat().getFormat("yy/MM/dd HH:mm:ss"));            cell.setCellStyle(cs);

            // ... other
        }

        try(OutputStream outputStream = new FileOutputStream("excel_test.xls")){
            wb.write(outputStream);
            wb.close();
        }catch(IOException e){
            e.printStackTrace();
        }
    }

    /**
     * return:
     * Map: {
     *     表名: data
     *     <String></String>: List<List<Object>>
     * }
     * List[0] = List<Object>, 表头, 均为String类型,解析时注意
     * List[1-end] = List<Object>, 数据,返回数据均作为字符串处理
     */
    @Test
    public void readTestFun(){
        String filename = "F:\\tmp\\learn_repo\\excel_demo\\excel_read_test.xlsx";
        Map<String, Object> mp = new HashMap<String, Object>();
        // 文件格式检验... 略

        File file = new File(filename);
        try {
            Workbook wb = WorkbookFactory.create(file);
            Iterator<Sheet> sheetIterator = wb.sheetIterator();
            while(sheetIterator.hasNext()){
                Sheet next = sheetIterator.next();
                List<List<Object>> res = new ArrayList<>();
                Iterator<Row> rowIterator = next.rowIterator();
                while(rowIterator.hasNext()){
                    Row tRow = rowIterator.next();
                    Iterator<Cell> cellIterator = tRow.cellIterator();
                    List<Object> rowList = new ArrayList<>();
                    while(cellIterator.hasNext()){
                        Cell cell = cellIterator.next();
                        CellType cellTypeEnum = cell.getCellTypeEnum();
                        if(cellTypeEnum == CellType.STRING) {
//                            System.out.println(cell.getStringCellValue());
                            rowList.add(cell.getStringCellValue());
                        }else if(cellTypeEnum == CellType.NUMERIC){
                            rowList.add(cell.getNumericCellValue());
                        }else if (cellTypeEnum == CellType.BOOLEAN){
                            rowList.add(cell.getBooleanCellValue());
                        }else if(cellTypeEnum == CellType.FORMULA){
                            // 公式不处理
                            rowList.add(new String());
                        }
                        else{
                            // 其他数据均看做非法数据
                            rowList.add(cell.getErrorCellValue());
                        }
                    }
                    res.add(rowList);
                }
                mp.put(next.getSheetName(), res);
            }
        } catch (IOException e) {
            e.printStackTrace();
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        }
        // output
        for(Map.Entry<String, Object> entry : mp.entrySet()){
            System.out.println("表名：" + entry.getKey());
            List<List<Object>> data = (List<List<Object>>) entry.getValue();
            for(int i = 0; i < data.size(); i++){
                if (i == 0) {
                    System.out.println("header: ");
                }

                List<Object> rowData = data.get(i);
                for (int j = 0; j < rowData.size(); j++){
                    System.out.print(rowData.get(j) + " ");
                }
                System.out.println();
            }
        }
    }



    @Test
    public void dateTestFun() throws IOException {
        Date now = new Date();
        System.out.println(now);
        File file = new File("a.txt");
        System.out.println(file.exists());
        boolean newFile = file.createNewFile();
        System.out.println(file.exists());
        System.out.println(file);
    }

    @Test
    public void readTestFun2() throws Exception {
        String filename = "F:\\tmp\\learn_repo\\data.xlsx";
        InventoryExcelManager2 inventoryExcelManager2 = new InventoryExcelManager2();
        System.out.println("load start");
        Map<String, List<List<Object>>> mp = inventoryExcelManager2.loadExcel(filename, 0);
        System.out.println("load finish");
        // 数据较多，输出到文件
//        OutputStream outputStream = new FileOutputStream("out.txt");
        PrintStream printStream = new PrintStream("out.txt");
        // output
        for(Map.Entry<String, List<List<Object>>> entry : mp.entrySet()){
//            System.out.println("表名：" + entry.getKey());
            printStream.println("表名：" + entry.getKey());
            List<List<Object>> data = (List<List<Object>>) entry.getValue();
            for(int i = 0; i < data.size(); i++){
                if (i == 0) {
//                    System.out.println("header: ");
                    printStream.println("header: ");
                }

                List<Object> rowData = data.get(i);
                for (int j = 0; j < rowData.size(); j++){
//                    System.out.print(rowData.get(j) + " ");
                    printStream.print(rowData.get(j) + " ");
                }
                System.out.println();
            }
        }
    }

}
